package util;

/**
 * Created by Administrator on 2017/8/18.
 */

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SQLUtil {
    public static Connection conn = null;
    public static PreparedStatement statement = null;
    public static ResultSet rs = null;

    //走ssh 隧道
    public static void goSSHTunnel() {
        try {
            JSch jsch = new JSch();
            Session session = jsch.getSession("yunshouhu", "192.168.0.102", 22);
            session.setPassword("xxxx");
            session.setConfig("StrictHostKeyChecking", "no");
            session.connect();
            System.out.println(session.getServerVersion());//这里打印SSH服务器版本信息

            //ssh -L 192.168.0.102:5555:192.168.0.101:3306 yunshouhu@192.168.0.102  正向代理
            int assinged_port = session.setPortForwardingL("192.168.0.101", 5555, "192.168.0.101", 3306);//端口映射 转发

            System.out.println("localhost:" + assinged_port);

            //ssh -R 192.168.0.102:5555:192.168.0.101:3306 yunshouhu@192.168.0.102
            //session.setPortForwardingR("192.168.0.102",5555, "192.168.0.101", 3306);
            // System.out.println("localhost:  -> ");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    // connect to MySQL
    public static void connSQL() {
        String url = "jdbc:mysql://localhost:3306/dyt?characterEncoding=UTF-8";
        String username = "root";
        String password = "dyt@88352636"; // 加载驱动程序以连接<a href="http://lib.csdn.net/base/mysql" class='replace_word' title="MySQL知识库" target='_blank' style='color:#df3434; font-weight:bold;'>数据库</a>
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, username, password);
            System.out.println("数据库链接正常");
        }
        //捕获加载驱动程序异常
        catch (ClassNotFoundException cnfex) {
            System.err.println(
                    "装载 JDBC/ODBC 驱动程序失败。");
            cnfex.printStackTrace();
        }
        //捕获连接数据库异常
        catch (SQLException sqlex) {
            System.err.println("无法连接数据库");
            sqlex.printStackTrace();
        }
    }

    // disconnect to MySQL
    public static void deconnSQL() {
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
            System.out.println("关闭数据库问题 ：");
            e.printStackTrace();
        }
    }

    // execute selection language
    public static ResultSet selectSQL(String sql) {

        try {
            statement = conn.prepareStatement(sql);
            rs = statement.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    // execute insertion language
    public static boolean insertSQL(String sql) {
        try {
            statement = conn.prepareStatement(sql);
            statement.executeUpdate();
            return true;
        } catch (SQLException e) {
            System.out.println("插入数据库时出错：");
            e.printStackTrace();
        } catch (Exception e) {
            System.out.println("插入时出错：");
            e.printStackTrace();
        }
        return false;
    }

    //execute delete language
    public static boolean deleteSQL(String sql) {
        try {
            statement = conn.prepareStatement(sql);
            statement.executeUpdate();
            return true;
        } catch (SQLException e) {
            System.out.println("插入数据库时出错：");
            e.printStackTrace();
        } catch (Exception e) {
            System.out.println("插入时出错：");
            e.printStackTrace();
        }
        return false;
    }

    //execute update language
    public static boolean updateSQL(String sql) {
        try {
            statement = conn.prepareStatement(sql);
            statement.executeUpdate();
            return true;
        } catch (SQLException e) {
            System.out.println("插入数据库时出错：");
            e.printStackTrace();
        } catch (Exception e) {
            System.out.println("插入时出错：");
            e.printStackTrace();
        }
        return false;
    }

    // show data in ju_users
    public static void layoutStyle2(ResultSet rs) {
        System.out.println("-----------------");
        System.out.println("执行结果如下所示:");
        System.out.println("-----------------");
        System.out.println(" 第一列" + "/t/t" + "第二列" + "/t/t" + "第三列" + "/t/t" + "第四列");
        System.out.println("-----------------");
        try {
            while (rs.next()) {
//                System.out.println(
//                        rs.getInt("id") + "/t/t"
//                        + rs.getString("name") + "/t/t"
//                        + rs.getString("short_name")
//                        + "/t/t"+ rs.getInt("department_id"));
//            }
                System.out.println(
                        rs.getString(1) + rs.getString(2) + rs.getString(3) + rs.getString(4));
            }
        } catch (SQLException e) {
            System.out.println("显示时数据库出错。");
            e.printStackTrace();
        } catch (Exception e) {
            System.out.println("显示出错。");
            e.printStackTrace();
        }
    }

    public static List convertList(ResultSet rs) throws SQLException {
        List list = new ArrayList();
        ResultSetMetaData md = rs.getMetaData();
        int columnCount = md.getColumnCount();
        while (rs.next()) {
            Map rowData = new HashMap();
            for (int i = 1; i <= columnCount; i++) {
//                System.out.println(md.getColumnName(i));
//                这里不用name 的原因是 同样的列名无法区分（AS 不生效）
                //               rowData.put(md.getColumnName(i), rs.getObject(i));
                rowData.put(md.getColumnLabel(i), rs.getObject(i));
            }
            list.add(rowData);
        }
        return list;
    }

}
